--EXEC SP_SAVE_ASSEMBLY_BOM_RELEASE_LOT_INFO , 5000, 1, 100


ALTER PROCEDURE SP_SAVE_ASSEMBLY_BOM_RELEASE_LOT_INFO 
(
@ASSEMBLY_RELEASE_ID INT,
@LOT_ID INT,
@WAFER_ID INT,
@QUANTITY_PULLED INT
)
AS
BEGIN

	DECLARE @LOT_NUMBER VARCHAR(50)
	DECLARE @PREVIOULY_PULLED_QTY INT
	DECLARE @DIFFERENCE_QTY INT

	DECLARE @LOT_QTY_IN			INT
	DECLARE @LOT_QTY_BALANCE	INT
	DECLARE @IS_BOM_INV_PULLING_BY_WAFER_ID VARCHAR(100)
	
	SET @PREVIOULY_PULLED_QTY = 0
	SET @DIFFERENCE_QTY = 0

	SELECT @LOT_NUMBER = LOT_NUMBER, @LOT_QTY_IN = QTY_IN FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
	SELECT @LOT_QTY_BALANCE = IL_QTY_BALANCE  FROM INVENTORY_LOT WHERE LOT_ID  = @LOT_ID

	SET @IS_BOM_INV_PULLING_BY_WAFER_ID = 'FALSE'
	SELECT @IS_BOM_INV_PULLING_BY_WAFER_ID = PROPERTY_VALUE FROM CONFIGURATION_TABLE WHERE PROPERTY_KEY = 'SCALAR.CHIPTRACK.OPERATIONS.RELEASE.ISBOMPULLINGBYWAFERID'

	IF ( @IS_BOM_INV_PULLING_BY_WAFER_ID = 'TRUE' )
	BEGIN
		IF ( ( SELECT COUNT(*) FROM BOM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID AND LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID ) > 0 )
		BEGIN
			--GETTING PREVIOUSLY PULLED QTY
			SELECT @PREVIOULY_PULLED_QTY = QTY_PULLED FROM BOM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID AND LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID
			IF ( @QUANTITY_PULLED = 0 )
			BEGIN
				--DELETING THE RECORD AS THE QUANTITY PULLED BECAME ZERO
				DELETE FROM BOM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID AND LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID 
				--ADDING (RETUNING BACK) THE PREVIOULY PULLED.
				UPDATE SORT_BIN_RELEASE_TABLE SET BIN_1_BALANCE_QTY = ( BIN_1_BALANCE_QTY + @PREVIOULY_PULLED_QTY ) WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID			
			END	
			ELSE
			BEGIN
				--UPDATING THE QUANTITY PULLED.
				UPDATE BOM_RELEASE_LOT_TABLE SET QTY_PULLED = @QUANTITY_PULLED WHERE ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID AND LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID
				SET @DIFFERENCE_QTY = @PREVIOULY_PULLED_QTY - @QUANTITY_PULLED 				
				--ADDITION OR SUBTRACTION WILL BE DONE HERE. IF PREVIUOLY PULLED QTY IS MORE MEANS 
				UPDATE SORT_BIN_RELEASE_TABLE SET BIN_1_BALANCE_QTY = ( BIN_1_BALANCE_QTY + @DIFFERENCE_QTY ) WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID
			END
		END
		ELSE
		BEGIN
			IF ( @QUANTITY_PULLED > 0 )
			BEGIN		
				--INSERTING THE RECORD AS THERE IS NO RECORD FOR THE GIVEN COMBINATION OF ASSEMBLY RELEASE ID, LOT ID AND WAFER ID 
				INSERT INTO BOM_RELEASE_LOT_TABLE ( ASSEMBLY_RELEASE_ID, LOT_ID, WAFER_ID, QTY_PULLED ) VALUES ( @ASSEMBLY_RELEASE_ID, @LOT_ID, @WAFER_ID, @QUANTITY_PULLED )				
				--REDUCING THE THE BIN1 BALANCE QUANTITY.
				UPDATE SORT_BIN_RELEASE_TABLE SET BIN_1_BALANCE_QTY = ( BIN_1_BALANCE_QTY - @QUANTITY_PULLED ) WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID
			END

		END
    	IF( (SELECT COUNT(*) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID AND BIN_1_BALANCE_QTY > 0 ) = 0 )
    	BEGIN
        	UPDATE LOT_TABLE SET LOT_CURRENT_STATUS='Close' WHERE LOT_ID = @LOT_ID
    	END
    	ELSE
		BEGIN
			UPDATE LOT_TABLE SET LOT_CURRENT_STATUS='Running' WHERE LOT_ID = @LOT_ID
		END
	END
	ELSE -- NOT BY WAFER ID
	BEGIN
		IF ( ( SELECT COUNT(*) FROM BOM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID AND LOT_ID = @LOT_ID ) > 0 )
		BEGIN
			--GETTING PREVIOUSLY PULLED QTY
			SELECT @PREVIOULY_PULLED_QTY = ISNULL( QTY_PULLED, 0) FROM BOM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID AND LOT_ID = @LOT_ID

			IF ( @QUANTITY_PULLED = 0 )
			BEGIN
				--DELETING THE RECORD AS THE QUANTITY PULLED BECAME ZERO
				DELETE FROM BOM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID AND LOT_ID = @LOT_ID
			END
			ELSE
			BEGIN
				--UPDATING THE QUANTITY PULLED.
				UPDATE BOM_RELEASE_LOT_TABLE SET QTY_PULLED = @QUANTITY_PULLED WHERE ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID AND LOT_ID = @LOT_ID
			END

			UPDATE LOT_TABLE SET QTY_IN = ( ( QTY_IN + @PREVIOULY_PULLED_QTY ) - @QUANTITY_PULLED ) WHERE LOT_ID = @LOT_ID
			UPDATE INVENTORY_LOT SET IL_QTY_IN = ( ( ISNULL( IL_QTY_IN, @LOT_QTY_IN ) + @PREVIOULY_PULLED_QTY ) - @QUANTITY_PULLED ), IL_QTY_BALANCE = ( ( ISNULL( IL_QTY_BALANCE, ISNULL( IL_QTY_IN, @LOT_QTY_IN ) ) + @PREVIOULY_PULLED_QTY ) - @QUANTITY_PULLED ) WHERE LOT_ID = @LOT_ID
		END
		ELSE
		BEGIN
			IF ( @QUANTITY_PULLED > 0 )
			BEGIN
				--INSERTING THE RECORD AS THERE IS NO RECORD FOR THE GIVEN COMBINATION OF ASSEMBLY RELEASE ID, LOT ID AND WAFER ID
				INSERT INTO BOM_RELEASE_LOT_TABLE ( ASSEMBLY_RELEASE_ID, LOT_ID, QTY_PULLED ) VALUES ( @ASSEMBLY_RELEASE_ID, @LOT_ID, @QUANTITY_PULLED )

				UPDATE LOT_TABLE SET QTY_IN = ( QTY_IN - @QUANTITY_PULLED ) WHERE LOT_ID = @LOT_ID
				UPDATE INVENTORY_LOT SET IL_QTY_IN = ( ISNULL( IL_QTY_IN, @LOT_QTY_IN ) - @QUANTITY_PULLED ), IL_QTY_BALANCE = ( ISNULL( IL_QTY_BALANCE, ISNULL( IL_QTY_IN, @LOT_QTY_IN ) ) - @QUANTITY_PULLED ) WHERE LOT_ID = @LOT_ID
			END
		END
		IF( ( SELECT COUNT(*) FROM LOT_TABLE LT, INVENTORY_LOT IL WHERE LT.LOT_ID = IL.LOT_ID AND LT.LOT_ID = @LOT_ID AND IL_QTY_BALANCE = 0 ) > 0 )
		BEGIN
    		UPDATE LOT_TABLE SET LOT_CURRENT_STATUS='Close' WHERE LOT_ID = @LOT_ID
		END
		ELSE
		BEGIN
			UPDATE LOT_TABLE SET LOT_CURRENT_STATUS='Running' WHERE LOT_ID = @LOT_ID
		END
	END
END